package com.xk.dao.impl;

import com.mysql.jdbc.Connection;
import com.xk.dao.StudentDao;
import com.xk.po.Course;
import com.xk.po.Student;
import com.xk.po.Teach;
import com.xk.po.Teacher;
import com.xk.util.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    @Override
    public List<Course> listCourse(String cname) {
        List<Course> list = new ArrayList();
        StringBuffer sql = new StringBuffer("select * from course where 1=1 ");
        if (cname!=null && !cname.equals("")){
            sql.append("and cname like '%").append(cname).append("%' ");
        }
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            rs = pst.executeQuery();
            while(rs.next()) {
                Course course = new Course();
                course.setCid(rs.getInt("cid"));
                course.setCname(rs.getString("cname"));
                list.add(course);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return list;
    }

    @Override
    public List<Teacher> listTeacher(String tname) {
        List<Teacher> list = new ArrayList();
        StringBuffer sql = new StringBuffer("select * from teacher where 1=1 ");
        if (tname!=null && !tname.equals("")){
            sql.append("and tname like '%").append(tname).append("%' ");
        }
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            rs = pst.executeQuery();
            while(rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTid(rs.getInt("tid"));
                teacher.setTname(rs.getString("tname"));
                teacher.setAge(rs.getInt("age"));
                teacher.setCid(rs.getInt("cid"));
                list.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return list;
    }

    @Override
    public Student getStudentByIdByPass(Integer sid, String password) {
        Student student = null;
        String sql = "select * from student where sid=? and password=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, sid);
            pst.setString(2, password);
            rs = pst.executeQuery();
            while(rs.next()) {
                student = new Student();
                student.setSid(rs.getInt("sid"));
                student.setSname(rs.getString("sname"));
                student.setBirth(rs.getInt("birth"));
                student.setSsex(rs.getBoolean("ssex"));
                student.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return student;
    }

    @Override
    public Student getStudentById(Integer sid) {
        Student student = null;
        String sql = "select * from student where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, sid);
            rs = pst.executeQuery();
            while(rs.next()) {
                student = new Student();
                student.setSid(rs.getInt("sid"));
                student.setSname(rs.getString("sname"));
                student.setBirth(rs.getInt("birth"));
                student.setSsex(rs.getBoolean("ssex"));
                student.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return student;
    }

    @Override
    public Teach getTeachById(Integer sid) {
        Teach teach = null;
        String sql = "select * from teach where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, sid);
            rs = pst.executeQuery();
            while(rs.next()) {
                teach = new Teach();
                teach.setSid(rs.getInt("sid"));
                teach.setCid(rs.getInt("cid"));
                teach.setTid(rs.getInt("tid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return teach;
    }

    @Override
    public List<Teach> listTeach(Integer sid) {
        List<Teach> list = new ArrayList();
        StringBuffer sql = new StringBuffer("select * from teach where sid=? ");

        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, sid);
            rs = pst.executeQuery();
            while(rs.next()) {
                Teach teach = new Teach();
                teach.setTid(rs.getInt("tid"));
                teach.setCid(rs.getInt("cid"));
                teach.setSid(rs.getInt("sid"));
                teach.setRecord_id(rs.getInt("record_id"));
                list.add(teach);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return list;
    }

    @Override
    public int insertTeach(Integer sid, Integer cid, Integer tid) {
        int record_id = 0;
        String sql = "insert into teach(sid,cid,tid) value(?,?,?)";
        try {
            con = DBUtil.getConnection();

            pst = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setInt(1, sid);
            pst.setInt(2, cid);
            pst.setInt(3, tid);
            pst.executeUpdate();
            rs = pst.getGeneratedKeys();
            if (rs.next()){
                record_id = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, pst, con);
        }
        return record_id;
    }

    @Override
    public int removeTeach(Integer record_id) {
        int result = 0;
        String delSql = "delete from Teach where record_id=?";
        try {
            con = DBUtil.getConnection();
            //开启一个事务
            con.setAutoCommit(false);
            //删除表
            pst = con.prepareStatement(delSql);
            pst.setInt(1, record_id);
            result = pst.executeUpdate();
            //提交事务
            con.commit();

        } catch (SQLException e) {
            result = 0;
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtil.close(null, pst, con);
        }
        return result;
    }

    @Override
    public int updateTeach(Teach teach) {
        int result = 0;
        String sql = "update teach set cid=?, tid=? where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, teach.getCid());
            pst.setInt(2, teach.getTid());
            pst.setInt(3, teach.getSid());
            result = pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null, pst, con);
        }
        return result;
    }

    @Override
    public int updateStudentByPassword(Integer sid, String password) {
        int result = 0;
        String sql = "update student set password=? where sid=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, password);
            pst.setInt(2, sid);
            result = pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null, pst, con);
        }
        return result;
    }
}
